package ro.jproject.imc.model;


import java.sql.*;

import ro.jproject.imc.view.AddProfessorGUI;
import ro.jproject.imc.view.AddStudentGUI;
import ro.jproject.imc.view.ShowProfessorsDataBase;
import ro.jproject.imc.view.ShowStudentsDataBase;
import ro.jproject.imc.view.ShowUniversityDataBase;
import ro.jproject.imc.view.UpdateStudentsDataBase;


public class DataBaseConnection {

	public static Connection connection = null;
	private Statement statement = null;
	public static Statement statementInsert = null;
	private String driver = "com.mysql.jdbc.Driver";
	private String dataBaseName = "ums";
	private String userName = "root";
	private String password = "";
	

	public boolean connected() {
		boolean isConnected = false;
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection("jdbc:mysql://"
					+ "localhost:3306/" + dataBaseName, userName, password);
			isConnected = true;
			System.out.println("Connected to database: " + dataBaseName);
		} catch (Exception e) {
			System.out.println("Can not connect to database! ");
		}
		return isConnected;
	}

// show students database
	public void queryE() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM students";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		new ShowStudentsDataBase(null, null, null, null, null);
		int rows = 0;
		while (rs.next()) {
			// Retrieve by column name
			String id = rs.getString("id");
			String firstName = rs.getString("firstName");
			String lastName = rs.getString("lastName");
			String status = rs.getString("status");
			String cnp = rs.getString("cnp");

			// Display values
			// System.out.print("Id: " + id +
			// ", First Name: " + firstName +
			// ", Last Name: " + lastName +
			// ", Status: " + status +
			// ", CNP: " + cnp + "\n");
			//
			
			
			
			ShowStudentsDataBase.model.addRow(new Object[] {});
			ShowStudentsDataBase.table.setValueAt(id, rows, 0);
			ShowStudentsDataBase.table.setValueAt(firstName, rows, 1);
			ShowStudentsDataBase.table.setValueAt(lastName, rows, 2);
			ShowStudentsDataBase.table.setValueAt(cnp, rows, 3);
			ShowStudentsDataBase.table.setValueAt(status, rows, 4);
			
			rows++;
			
			
		}
		
		
		rs.close();
		statement.close();
		connection.close();
	}

//	show professors database
	public void queryShowProfessorsDataBase() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM professors";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		new ShowProfessorsDataBase(null, null, null, null, null, null);
		int rows = 0;
		while (rs.next()) {
			// Retrieve by column name
			String id = rs.getString("id");
			String firstName = rs.getString("firstName");
			String lastName = rs.getString("lastName");
			String title = rs.getString("title");
			String field = rs.getString("field");
			String cnp = rs.getString("cnp");

			// Display values
			// System.out.print("Id: " + id +
			// ", First Name: " + firstName +
			// ", Last Name: " + lastName +
			// ", Status: " + status +
			// ", CNP: " + cnp + "\n");
			//

			
			ShowProfessorsDataBase.model.addRow(new Object[] {});
			ShowProfessorsDataBase.table.setValueAt(id, rows, 0);
			ShowProfessorsDataBase.table.setValueAt(firstName, rows, 1);
			ShowProfessorsDataBase.table.setValueAt(lastName, rows, 2);
			ShowProfessorsDataBase.table.setValueAt(cnp, rows, 3);
			ShowProfessorsDataBase.table.setValueAt(title, rows, 4);
			ShowProfessorsDataBase.table.setValueAt(field, rows, 5);
				
			rows++;		
			
		}
		
		rs.close();
		statement.close();
		connection.close();
	}
	
//	show faculty
	public void queryShowUniversityDataBase() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM university";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		
		AddProfessorGUI.comboBoxFaculty.removeAllItems();
		AddProfessorGUI.comboBoxFaculty.addItem("Select faculty");
		
		new ShowUniversityDataBase(null, null, null);
		int rows = 0;
		while (rs.next()) {
			// Retrieve by column name
			
			
			
			String id = rs.getString("id");
			String facultyName = rs.getString("facultyName");
			String addressName = rs.getString("addressName");
			

			ShowUniversityDataBase.model.addRow(new Object[] {});
			ShowUniversityDataBase.table.setValueAt(id, rows, 0);
			ShowUniversityDataBase.table.setValueAt(facultyName, rows, 1);
			ShowUniversityDataBase.table.setValueAt(addressName, rows, 2);
			
			
			rows++;		
			
			AddProfessorGUI.comboBoxFaculty.addItem(facultyName);
			
			
		}
	
		
		
		rs.close();
		statement.close();
		connection.close();
	}

//	show groups database

	public void queryShowGroupsDataBase() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM groups";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		AddStudentGUI.comboBoxGroup.removeAllItems();
		AddStudentGUI.comboBoxGroup.addItem("Select");
		while (rs.next()) {
			// Retrieve by column name
			
			String groupsNo = rs.getString("groupNo");
			
			AddStudentGUI.comboBoxGroup.addItem(groupsNo);

			
		}
		
		rs.close();
		statement.close();
		connection.close();
	}

//	show faculty database

	public void queryShowFacultyDataBase() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM university";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		AddStudentGUI.comboBoxFaculty.removeAllItems();
		AddStudentGUI.comboBoxFaculty.addItem("Select");
		while (rs.next()) {
			// Retrieve by column name
			
			String facultyName = rs.getString("facultyName");
			
			AddStudentGUI.comboBoxFaculty.addItem(facultyName);

			
		}
		
		rs.close();
		statement.close();
		connection.close();
	}

// show specialization data base


	public void queryShowSpecializationDataBase() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM specialization";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		AddStudentGUI.comboBoxSpecialization.removeAllItems();
		AddStudentGUI.comboBoxSpecialization.addItem("Select");
		while (rs.next()) {
			// Retrieve by column name
			
			String specialization = rs.getString("specializationName");
			
			AddStudentGUI.comboBoxSpecialization.addItem(specialization);

			
		}
		
		rs.close();
		statement.close();
		connection.close();
	}

// insert students in database
	public void insertQueryStudents(String firstName, String lastName, String cnp, String status) throws SQLException {

		System.out.println("Inserting students records into the table");
		statementInsert = connection.createStatement();

		String insertIntoDataBase = "INSERT INTO students(firstName, lastName, cnp, status) VALUES ('"
				+ firstName
				+ "', '"
				+ lastName
				+ "', '"
				+ cnp
				+ "', '"
				+ status + "'" + ")";

		System.out.println(insertIntoDataBase);

		statementInsert.executeUpdate(insertIntoDataBase);

	}
	
// insert professors in database
	public void insertQueryProfesors(String firstName, String lastName, String cnp, String title, String field) throws SQLException {
		System.out.println("Inserting profesors records into the table");
		statementInsert = connection.createStatement();

		String insertIntoDataBase = "INSERT INTO profesors(firstName, lastName, cnp, title, field) VALUES ('"
				+ firstName
				+ "', '"
				+ lastName
				+ "', '"
				+ cnp
				+ "', '"
				+ title 
				+ "', '"
				+ field + "'" + ")";

		System.out.println(insertIntoDataBase);

		statementInsert.executeUpdate(insertIntoDataBase);

	}
	
//	insert faculty in database
	public void insertQueryFaculty(String facultyName, String addressName) throws SQLException {
		System.out.println("Inserting profesors records into the table");
		statementInsert = connection.createStatement();

		String insertIntoDataBase = "INSERT INTO university(facultyName, addressName) VALUES ('"
				+ facultyName
				+ "', '"
				+ addressName
				+ "'" + ")";

		System.out.println(insertIntoDataBase);

		statementInsert.executeUpdate(insertIntoDataBase);

	}
	
//	insert students registration in database

	public void insertQueryStudentsRegistration(String firstName, String lastName, String cnp, String status, String facultyName, String specialization, 
												String registrationYear, String yearOfStudy, String group) throws SQLException {
	
		System.out.println("Inserting students registration records into the table");
		statementInsert = connection.createStatement();

		String insertIntoDataBase = "INSERT INTO studentsregistration (firstName, lastName, cnp, status, facultyName, specialization,"
																   + " registrationYear, yearOfStudy, `group`)" +
									" VALUES ('"
												+ firstName
												+ "', '"
												+ lastName
												+ "', '"
												+ cnp
												+ "', '"
												+ status
												+ "', '"
												+ facultyName
												+ "', '"
												+ specialization
												+ "', '"
												+ registrationYear
												+ "', '"
												+ yearOfStudy
												+ "', '"
												+ group
												+ "'" + ")";

		System.out.println(insertIntoDataBase);

		statementInsert.executeUpdate(insertIntoDataBase);

	}
	
//	delete students from database
	
	public void deleteQueryStudents(String firstName, String lastName, String cnp) throws SQLException {

		System.out.println("Deleting student record from the table");
		statementInsert = connection.createStatement();

		String deleteStudentFromDataBase = "DELETE FROM students WHERE firstName='"
				+ firstName
				+"' AND lastName='"
				+ lastName
				+ "' AND cnp='"
				+ cnp
				+ "'";

		System.out.println(deleteStudentFromDataBase);
		
		statementInsert.executeUpdate(deleteStudentFromDataBase);

	}
	
//	update students database & show students database
	public void updateQueryStudents(String id, String firstName, String lastName, String cnp, String status, String group, String faculty) throws SQLException {

		System.out.println("Updating student database");
		statementInsert = connection.createStatement();

		String deleteStudentFromDataBase = "UPDATE studentsregistration SET " +
				"							firstName='" + firstName 
											+ "', lastName='" + lastName 
											+ "', cnp='" + cnp
											+ "', status='" + status 
											+ "', facultyName='" + faculty 
											+ "', `group`='" + group
											+ "' WHERE id='"
											+ id
											+ "'";

		System.out.println(deleteStudentFromDataBase);
		
		statementInsert.executeUpdate(deleteStudentFromDataBase);

	}
	
	public void queryUpdate() throws SQLException {

		System.out.println("Creating statement...");
		statement = connection.createStatement();
		String sql;
		sql = "SELECT * FROM studentsregistration";
		ResultSet rs = statement.executeQuery(sql);

		System.out.println("Data base: " + dataBaseName);

		new UpdateStudentsDataBase(null, null, null, null, null);
		int rows = 0;
		while (rs.next()) {
			// Retrieve by column name
			String id = rs.getString("id");
			String firstName = rs.getString("firstName");
			String lastName = rs.getString("lastName");
			String status = rs.getString("status");
			String cnp = rs.getString("cnp");
			String faculty = rs.getString("facultyName");
			String specialization = rs.getString("specialization");
			String registrationYear = rs.getString("registrationYear");
			String yearOfStudy = rs.getString("yearOfStudy");
			String group = rs.getString("group");
			
			
			// Display values
			// System.out.print("Id: " + id +
			// ", First Name: " + firstName +
			// ", Last Name: " + lastName +
			// ", Status: " + status +
			// ", CNP: " + cnp + "\n");
			//
			
			
			
			UpdateStudentsDataBase.model.addRow(new Object[] {});
			UpdateStudentsDataBase.table.setValueAt(id, rows, 0);
			UpdateStudentsDataBase.table.setValueAt(firstName, rows, 1);
			UpdateStudentsDataBase.table.setValueAt(lastName, rows, 2);
			UpdateStudentsDataBase.table.setValueAt(cnp, rows, 3);
			UpdateStudentsDataBase.table.setValueAt(status, rows, 4);
			UpdateStudentsDataBase.table.setValueAt(faculty, rows, 5);
			UpdateStudentsDataBase.table.setValueAt(specialization, rows, 6);
			UpdateStudentsDataBase.table.setValueAt(registrationYear, rows, 7);
			UpdateStudentsDataBase.table.setValueAt(yearOfStudy, rows, 8);
			UpdateStudentsDataBase.table.setValueAt(group, rows, 9);
			
			rows++;
			
			
		}
		
		
		rs.close();
		statement.close();
		connection.close();
	}


}
